﻿using MiniExcelLibs;
using Sunny.UI;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PanhuaWin.超级管理员
{
    public partial class Form重庆发岳阳收货情况 : UIPage
    {
        public Form重庆发岳阳收货情况()
        {
            InitializeComponent();
        }

        private void uiButton查询_Click(object sender, EventArgs e)
        {
            DataTable dt = 查询(uiTextBox船次.Text.Trim());
            uiDataGridView1.DataSource = dt;
            uiDataGridView1.Refresh();
        }

        private DataTable 查询(string 船号)
        {
            string sql = $@"select * from (
                                    SELECT  
                                    panhuadb.dbo.Bus_DeliveryV.WorkShopName AS 工厂名称, panhuadb.dbo.Bus_DeliveryV.DocCode AS 发货单号, 
                                    panhuadb.dbo.Bus_DeliveryV.DeliveryDate AS 发货单日期, panhuadb.dbo.Bus_DeliveryV.DeliveryAddress AS 收货地址, 
                                    panhuadb.dbo.Bus_DeliveryV.DepartmentCode AS 部门编号, 
                                    panhuadb.dbo.Bus_DeliveryV.DepartmentName AS 部门名称, panhuadb.dbo.Bus_DeliveryV.CustomerCode AS 客户编号, 
                                    panhuadb.dbo.Bus_DeliveryV.CustomerName AS 客户名称,panhuadb.dbo.Bus_DeliveryV.ShipNo 销售船号, 
                                    panhuadb.dbo.Bus_DeliveryV.Remark AS 表头备注, 
                                    panhuadb.dbo.Bus_DeliveryVolume.VolumeNumber AS 卷号, 
                                    panhuadb.dbo.Bus_DeliveryVolume.InventoryCode AS NC存货编号, 
                                    panhuadb.dbo.Bus_DeliveryVolume.WarehouseCode AS 仓库编号, 
                                    panhuadb.dbo.Bus_DeliveryVolume.WarehouseName AS 仓库名称, 
                                    panhuadb.dbo.Bus_DeliveryVolume.Size AS FinishSize, panhuadb.dbo.Bus_DeliveryVolume.Weight AS 重量, 
                                    panhuadb.dbo.Bus_DeliveryVolume.status AS 行状态, panhuadb.dbo.Bus_DeliveryVolume.Modifier 发货员, 
                                    panhuadb.dbo.Bus_DeliveryVolume.ModifyDate 发货日期,
                                    panhuadb.dbo.Bus_DeliveryVolume.Chepai 发货车牌, 
                                    panhuadb.dbo.Bus_DeliveryVolume.收货状态, panhuadb.dbo.Bus_DeliveryVolume.收货时间, 
                                    panhuadb.dbo.Bus_DeliveryVolume.收货人
                                    FROM      panhuadb.dbo.Bus_DeliveryV INNER JOIN
                                    panhuadb.dbo.Bus_DeliveryVolume ON 
                                    panhuadb.dbo.Bus_DeliveryV.guid = panhuadb.dbo.Bus_DeliveryVolume.guid LEFT OUTER JOIN
                                    panhuadb.dbo.Bus_Volume ON 
                                    panhuadb.dbo.Bus_Volume.VolumeCodeNew = panhuadb.dbo.Bus_DeliveryVolume.VolumeNumber
                                    WHERE   (panhuadb.dbo.Bus_DeliveryV.CustomerName in ('湖南攀达新型材料有限公司','江苏华晟新型建材有限公司'))
                                    union all
                                    SELECT  
                                    zjgdb.dbo.Bus_DeliveryV.WorkShopName AS 工厂名称, zjgdb.dbo.Bus_DeliveryV.DocCode AS 发货单号, 
                                    zjgdb.dbo.Bus_DeliveryV.DeliveryDate AS 发货单日期, zjgdb.dbo.Bus_DeliveryV.DeliveryAddress AS 收货地址, 
                                    zjgdb.dbo.Bus_DeliveryV.DepartmentCode AS 部门编号, 
                                    zjgdb.dbo.Bus_DeliveryV.DepartmentName AS 部门名称, zjgdb.dbo.Bus_DeliveryV.CustomerCode AS 客户编号, 
                                    zjgdb.dbo.Bus_DeliveryV.CustomerName AS 客户名称,zjgdb.dbo.Bus_DeliveryV.ShipNo 销售船号, 
                                    zjgdb.dbo.Bus_DeliveryV.Remark AS 表头备注, 
                                    zjgdb.dbo.Bus_DeliveryVolume.VolumeNumber AS 卷号, 
                                    zjgdb.dbo.Bus_DeliveryVolume.InventoryCode AS NC存货编号, 
                                    zjgdb.dbo.Bus_DeliveryVolume.WarehouseCode AS 仓库编号, 
                                    zjgdb.dbo.Bus_DeliveryVolume.WarehouseName AS 仓库名称, 
                                    zjgdb.dbo.Bus_DeliveryVolume.Size AS FinishSize, zjgdb.dbo.Bus_DeliveryVolume.Weight AS 重量, 
                                    zjgdb.dbo.Bus_DeliveryVolume.status AS 行状态, zjgdb.dbo.Bus_DeliveryVolume.Modifier 发货员, 
                                    zjgdb.dbo.Bus_DeliveryVolume.ModifyDate 发货日期,
                                    zjgdb.dbo.Bus_DeliveryVolume.Chepai 发货车牌, 
                                    zjgdb.dbo.Bus_DeliveryVolume.收货状态, zjgdb.dbo.Bus_DeliveryVolume.收货时间, 
                                    zjgdb.dbo.Bus_DeliveryVolume.收货人
                                    FROM      zjgdb.dbo.Bus_DeliveryV INNER JOIN
                                    zjgdb.dbo.Bus_DeliveryVolume ON 
                                    zjgdb.dbo.Bus_DeliveryV.guid = zjgdb.dbo.Bus_DeliveryVolume.guid LEFT OUTER JOIN
                                    zjgdb.dbo.Bus_Volume ON 
                                    zjgdb.dbo.Bus_Volume.VolumeCodeNew = zjgdb.dbo.Bus_DeliveryVolume.VolumeNumber
                                    WHERE   (zjgdb.dbo.Bus_DeliveryV.CustomerName in ('湖南攀达新型材料有限公司','江苏华晟新型建材有限公司'))
                                    ) t where 1=1 ";
            if (string.IsNullOrWhiteSpace(船号) == false)
            {
                sql += $" and t.表头备注  like '%{船号}%'";
            }
            if (string.IsNullOrWhiteSpace(comboBox发货工厂.Text) == false)
            {
                sql += $" and t.工厂名称  = '{comboBox发货工厂.Text}'";
            }

            sql += $"and 发货日期 >= '{uiDatePicker开始日期.Value.ToString("yyyy-MM-dd")}' and 发货日期 < '{uiDatePicker结束日期.Value.ToString("yyyy-MM-dd")}'";

            DataTable dt = pub.GetDataTable(pub.ConnectionString,sql);
            return dt;
        }

        private void uiButton导出_Click(object sender, EventArgs e)
        {
            string path;
            this.saveFileDialog1.Filter = "Excel文件 (*.XLSX)|*.XLSX";
            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                path = this.saveFileDialog1.FileName;
                if (!File.Exists(path))
                {
                    MiniExcel.SaveAs(path, 查询(uiTextBox船次.Text.Trim()), true, "sheet1", ExcelType.XLSX);
                }
                else
                {
                    MessageBox.Show("文件已经存在,请重试!", "提示", MessageBoxButtons.OK);
                    return;
                }

            }
        }

        private void Form重庆发岳阳收货情况_Load(object sender, EventArgs e)
        {
            DateTime time = DateTime.Now;

            uiDatePicker开始日期.Value = time.AddDays(1 - time.Day);
            uiDatePicker结束日期.Value = time;

        }
    }
}
